using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using ClienteCaptura;

namespace ClienteCaptura.Core {

  public enum SearchField {
    Referencia,
    Mayorista,
    Centro,
    Ejecutivo,
    Capturista
  }

  public class Factura {

    private string referencia = string.Empty;
    private DateTime fecha_factura;
    private DateTime created_at;
    private DateTime updated_at;
    private int user_id = 0;
    private int centro_id = 0;
    private int ejecutivo_id = 0;
    private int mayorista_id = 0;
    private int id = 0;

    public string Referencia {
      get { return referencia; }
      set { referencia = value; }
    }

    public DateTime FechaFactura {
      get { return fecha_factura; }
      set { fecha_factura = value; }
    }
    
    public int UserId {
      get { return user_id; }
      set { user_id = value; }
    }
    
    public int CentroId {
      get { return centro_id; }
      set { centro_id = value; }
    }

    public int EjecutivoId {
      get { return ejecutivo_id; }
      set { ejecutivo_id = value; }
    }

    public int MayoristaId {
      get { return mayorista_id; }
      set { mayorista_id = value; }
    }

    public int Id {
      get { return id; }
    }


    public Factura() {
    }

    public static List<Factura> Search(SearchField search_field, string criteria) {
      string query = "SELECT * FROM facturas WHERE (";
      string condition = string.Empty;
      StringBuilder sb = new StringBuilder();

      switch(search_field) {
	// referencia LIKE '%criteria%'
        case SearchField.Referencia:
	  sb.AppendFormat("referencia LIKE '%{0}%')", criteria);
	  condition = sb.ToString();
	  break;
	// mayorista_id=criteria
	case SearchField.Mayorista:
	  sb.AppendFormat("mayorista_id={0})", criteria);
	  condition = sb.ToString();
	  break;
	// centro_id=criteria
	case SearchField.Centro:
	  sb.AppendFormat("centro_id={0})", criteria);
	  condition = sb.ToString();
	  break;
	// ejecutivo_id=criteria
	case SearchField.Ejecutivo:
	  sb.AppendFormat("ejecutivo_id={0})", criteria);
	  condition = sb.ToString();
	  break;
	// [user_id]=criteria
	case SearchField.Capturista:
	  sb.AppendFormat("[user_id]={0})", criteria);
	  condition = sb.ToString();
	  break;
      }

      StringBuilder complete_query =  new StringBuilder();

      complete_query.Append(query).Append(condition);

      string final_query = complete_query.ToString();

      // need a DataReader
      List<Factura> facturas = new List<Factura>();

        SqlDataReader reader = DatabaseHelper.ExecuteReader(final_query);

        while(reader.Read()) {

          facturas.Add(CreateFromReader(reader));

        }

      return facturas;
    }

    private static Factura CreateFromReader(SqlDataReader reader) {
          Factura factura = new Factura();

          factura.id = reader.GetInt32(0);
          factura.mayorista_id = reader.GetInt32(1);
          factura.fecha_factura = reader.GetDateTime(2);
          factura.referencia = reader.GetString(3);
          factura.created_at = reader.GetDateTime(4);
          factura.updated_at = reader.GetDateTime(5);
          factura.centro_id = reader.GetInt32(6);
          // fecha_proceso
          // status_id
          // old_id
          factura.user_id = reader.GetInt32(10);
          factura.ejecutivo_id = reader.GetInt32(11);

          return factura;
    }

    public bool Save() {
      string insert_query = "INSERT facturas(mayorista_id,fecha_factura,referencia, "+
      "created_at, updated_at,centro_id,[user_id],ejecutivo_id) VALUES (@mayorista_id, "+
      "@fecha_factura,@referencia,@created_at,@updated_at,@centro_id,@user_id,@ejecutivo_id)";
      string identity_query = "SELECT @@IDENTITY";
      string connection_string = DatabaseHelper.GetConnectionString();

      int row_count = 0;
      object identity = null;

      if (IsValid()) {
        using(SqlConnection connection = new SqlConnection(connection_string)) {
          connection.Open();
          SqlCommand command = new SqlCommand(insert_query, connection);
          command.CommandType = CommandType.Text;

          command.Parameters.Add("@mayorista_id", SqlDbType.Int).Value = mayorista_id;
          command.Parameters.Add("@fecha_factura", SqlDbType.DateTime).Value = fecha_factura;
          command.Parameters.Add("@referencia", SqlDbType.VarChar).Value = referencia;
          command.Parameters.Add("@created_at", SqlDbType.DateTime).Value = DateTime.Now;
          command.Parameters.Add("@updated_at", SqlDbType.DateTime).Value = DateTime.Now;
          command.Parameters.Add("@centro_id", SqlDbType.Int).Value = centro_id;
          command.Parameters.Add("@user_id", SqlDbType.Int).Value = user_id;
          command.Parameters.Add("@ejecutivo_id", SqlDbType.Int).Value = ejecutivo_id;

          try {

            row_count = command.ExecuteNonQuery();
          }
          catch {
            connection.Close();
            throw;
          }
          
          SqlCommand identity_command = new SqlCommand(identity_query, connection);
          identity_command.CommandType = CommandType.Text;

          try {
            
            identity = identity_command.ExecuteScalar();
          }
          catch {
            connection.Close();
            throw;
          }

        }

        id = Int32.Parse(identity.ToString());
      }

      return (id != 0) && (row_count == 1);
    }

    public static Factura Find(int factura_id) {
      string select_query = "SELECT * FROM facturas WHERE [id]=@id";
      SqlDataReader reader;
      string connection_string = DatabaseHelper.GetConnectionString();

      using(SqlConnection connection = new SqlConnection(connection_string)) {
        connection.Open();

        SqlCommand command = new SqlCommand(select_query, connection);
        command.CommandType = CommandType.Text;

        command.Parameters.Add("@id", SqlDbType.Int).Value = factura_id;

        try {
          reader = command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch {
          connection.Close();
          throw;
        }

      }

      return CreateFromReader(reader);
    }

    public bool IsValid() {
      return (centro_id != 0) && (ejecutivo_id != 0) && (user_id != 0)
        && (mayorista_id != 0) && (!referencia.Equals(string.Empty))
        && (fecha_factura != null);
    }

  }
}

